/*
 * 먼저 멤버 테이블에 각 그룹의 포인트를 담을 column을 추가한다.
 * 
 */
create table nu_member(
	id varchar2(100) primary key,
	password varchar2(100) not null,
	orgfilename varchar2(200),
	savefilename varchar2(200)
)

/*
 * alter table을 실시
 */
alter table nu_member add(
	study_point number default 500,
	sports_point number default 500,
	smoking_point number default 500,
	finance_point number default 500,
	living_point number default 500
);

alter table nu_member add(
	study_nick varchar2(100),
	sports_nick varchar2(100),
	smoking_nick varchar2(100),
	finance_nick varchar2(100),
	living_nick varchar2(100)
);

alter table nu_board add(
	nick_name varchar2(100)
);

alter table nu_reply add(
	nick_name varchar2(100)
);

select * from nu_member
/*
 * 칭호 테이블 생성
 * 칭호 번호
 * 맥시점
 * 미니멈
 * 칭호 이름
 * 칭호 내용
 * 칭호 타입
 */
create table nu_title(
	title_id number primary key,
	max_point number not null,
	min_point number not null,
	title_name varchar2(100) not null,
	title_content varchar2(200) not null,
	title_type varchar2(100) not null
)
select title_content from nu_title where title_name='초심자' and title_type='study'
create sequence nu_title_id_seq nocache;

/*
 * 각 게시판별 관리자
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,9999,9999,'공부관리자','공부 게시판 관리자입니다','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,9999,9999,'운동관리자','공부 게시판 관리자입니다','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,9999,9999,'금연관리자','공부 게시판 관리자입니다','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,9999,9999,'재테크관리자','공부 게시판 관리자입니다','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,9999,9999,'생활관리자','공부 게시판 관리자입니다','living');

/* --------------------------------------------------------------------------------------
 * study 500~899사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,899,700,'공부의 신','당신은 공부의 신입니다. 누구도 이길 자가 없네요','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,699,600,'엘리트','당신은 사회에서 반드시 필요한 사람입니다','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,599,560,'전교 1등','이렇게 하다가 전국 수석도 하시겠어요','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,559,520,'3등급','당신은 수능 3등급정도 되시네요','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,519,500,'초심자','당신은 공부 초심자입니다.','study');

/*
 * study 0~499사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,499,480,'노력해야할','조금 더 노력하면 공부에 흥미가 생길 거에요','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,479,440,'좀 모자란','당신은 좀 떨어지는 것 같군요','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,439,400,'기술 배워라','일찌감치 공부 접고 다른길 가셔야겠네요','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,399,200,'아메바','뇌가 존재하나요?','study');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,199,0,'루저','데이터가 아깝네요','study');

/* --------------------------------------------------------------------------------------
 * sports 500~899사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,899,700,'만수무강','인간 최대 수명 기네스북 후보','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,699,600,'병원 킬러','병원을 안가도 되는 당신, 병원이 싫어합니다','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,599,560,'몸짱','연예인하셔도 되겠어요','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,559,520,'건강한','당신은 몸도 마음도 건강한 사람이군요','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,519,500,'초심자','당신은 운동 초심자입니다.','sports');

/*
 * sports 0~499사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,499,480,'느림보','조금 더 달려보세요','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,479,440,'돼지','당신은 돼지입니다 꿀꿀','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,439,400,'몬스터','당신, 반지의제왕에서 본 듯 하네요','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,399,200,'평생 그렇게 살','저도 포기 했습니다','sports');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,199,0,'산소소모기','공기가 아깝습니다','sports');

/* --------------------------------------------------------------------------------------
 * smoking 500~899사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,899,700,'금연종결자','금연을 해낸 당신은 앞으로 무엇이든 할 수 있습니다','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,699,600,'담배를 물리친','당신은 드디어 담배를 끊었다고 말할 수 있습니다. 정말 대단합니다','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,599,560,'존경스러운 금연자','존경스럽습니다. 당신은 흡연 욕구의 최대 위기를 넘겼습니다','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,559,520,'자랑스러운 금연자','아직 몸이 적응이 안되고 힘들겠지만 당신은 담배 끊을 수 있습니다','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,519,500,'초심자','당신은 금연 초심자입니다.','smoking');

/*
 * smoking 0~499사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,499,480,'더 줄여야할','담배 노력하면 더 줄일 수 있어요','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,479,440,'헤비스모커','당신은 헤비스모커이군요','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,439,400,'예비 폐암환자','병원가서 검사나 받아라','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,399,200,'오늘내일할','남은 인생 남 피해 주지 말고 딴데가서 펴라','smoking');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,199,0,'故','ㅉㅉ...','smoking');

/* --------------------------------------------------------------------------------------
 * finance 500~899사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,899,700,'돈으로 똥 닦는','당신에게 돈은 의미가 없네요','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,699,600,'부르주아','당신은 부와 사회적 지위도 가지고 있는 사람입니다','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,599,560,'졸부','나름 좀 사는 듯','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,559,520,'근검절약하는','초심자를 벗어난 당신은 근검 절약 정신이 뛰어난 사람입니다','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,519,500,'초심자','당신은 재테크 초심자입니다.','finance');

/*
 * finance 0~499사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,499,480,'하층민','하층민이 될 가능성이 높으니 좀 더 노력하세요','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,479,440,'평생 로또나 살','당신은 평생 로또나 살 운명입니다','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,439,400,'신용불량자','언제부터 검색창에 신용불량자 검색했어요?','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,399,200,'수드라','당신은 노예 계층으로 신분상승은 꿈도 꾸지 마세요','finance');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,199,0,'그지','야 냄새나 꺼져','finance');

/* --------------------------------------------------------------------------------------
 * living 500~899사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,899,700,'인간문화재','당신은 인간문화재로 지정되어 존경할만한 사람입니다','living');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,699,600,'손발이 닳아 없어진 이병','지문 없어지겠어요','living');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,599,560,'혼자서도 잘해요','꺼야 꺼야 할꺼야 혼자서도 잘할꺼야','living');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,559,520,'부지런한','개미처럼 부지런하네요','living');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,519,500,'초심자','당신은 생활 초심자입니다.','living');

/*
 * living 0~499사이 칭호
 */
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,499,480,'작심삼일','당신은 조금 게으른 성격이시네요. 조금 더 노력하세요','living');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,479,440,'방해만 되는','당신은 다른 그룹원 활동에 방해가 되네요','living');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,439,400,'이 어플 왜 쓸까','이 어플은 당신에게 필요 없겠어요','living');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,399,200,'패가망신할','조심하세요 집 거덜내겠어요','living');
insert into nu_title (title_id,max_point,min_point,title_name,title_content,title_type) 
values (nu_title_id_seq.nextval,199,0,'똥 제조기','니가 살면서 가장 생산적이 활동은 똥 만들기','living');

/**
 * 테스트 영역
 */
select no, title, id, time_posted, hits, confirmation, group_id from (
		select no, title, id, to_char(time_posted,'YYYY.MM.DD') as time_posted, hits, confirmation, group_id, ceil(rownum/10) as page 
		from (select * from nu_board where group_id=1 order by no desc) ) where page=1
		
select title_name from nu_title where max_point>= 200 and min_point<=200 and title_type = 'study';
select * from NU_MEMBER

update nu_member set study_point=430 where id='test@nate.com';
update nu_member set smoking_point=650 where id='test@nate.com';
update nu_member set finance_point=420 where id='test@nate.com';
update nu_member set living_point=560 where id='test@nate.com';

select * from nu_reply